const express = require('express');
const bodyParser = require('body-parser');
const app = express();
const mysql = require('mysql2');
const http = require('http');  // 自己家的

app.use(bodyParser.json()); // 解析JSON请求体
app.get('/', (req, res) => {
  res.send('Hello World!'); // 发送响应内容
});

// 创建数据库连接池

// const pool = mysql.createConnection({
const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "root",
  database: "members_db",
  charset: "UTF8_GENERAL_CI",
  connectTimeout: 10000,
  multipleStatements: false // 是否允许一个 query 中有多个 MySQL 语句，默认为 false
});

connection.connect((err) => {
  if (err) {
    console.error("Error connecting to database:", err);
    return;
  }
  console.log("Connected to database");

  // 执行 SQL 查询,可以用??代替表名、字段、索引名；用?代替数据。
  const sql = "SELECT * FROM tb_member WHERE AGE > ?";
  const params = [18];
  connection.query(sql, params, (err, results) => {
	console.log("Connected to database22222222");
    if (err) {
      console.error("Error executing query:", err);
	  connection.end();
      return;
    }
    console.log("Query results:", results);
	// connection.end();
  });
  
  connection.execute('SELECT * FROM tb_member WHERE age > ?',[18],
  function(err, results, fields) {
    if (err) {
      console.log('[SELECT ERROR] - ', err.message);
	  connection.end();
      return;
    }
    console.log(results);
    console.log(fields);
	connection.end();
   }
  );
});

// 查询所有
app.get('/getAll', (req, res) => {
  const sql = 'SELECT * FROM tb_member';
  connection.query(sql, (err, results) => {
    if (err) return res.status(500).json(err);
    res.status(200).json(results);
  });
});
// 增加成员
app.post('/members', (req, res) => {
  const { name, age } = req.body;
  db.query('INSERT INTO tb_member (name, age) VALUES (?, ?)', [name, age], (err, result) => {
    if (err) throw err;
    res.send({ id: result.insertId, name, age });
   });
});



// 在数据库连接关闭时触发
connection.on("end", () => {
  console.log("Database connection closed");
});

// 在发生错误时触发
connection.on("error", (err) => {
  console.error("Database error:", err);
});

// 关闭数据库连接
// connection.end();


// 启动服务器监听3000端口，并打印启动信息
app.listen(3000, () => {
  console.log('Server running at http://127.0.0.1:3000');
});
